alter PROCEDURE [dbo].[UP_PCategory_GetChild]  
@CategoryID  int=0,  
@LanguageID INT=1,  
@BrandID  int  =  0  
  
AS  
BEGIN  
  
IF  @BrandID = 0  
SELECT * FROM dbo.PCategory WHERE ParentID=@CategoryID AND LanguageID=@LanguageID  AND  dbo.PCategory.CategoryID <>2147483647   
ORDER BY   SortOrder,CategoryID    
  
 ELSE   
   
 BEGIN   
  
with cte as  
(  
select distinct  p.CategoryID, c.ParentID,0 as Layer  
from Product p join PCategory c on p.CategoryID=c.CategoryID       
where p.BrandID=@BrandID  and c.LanguageID=@LanguageID  and p.IsDisplay=1  
union all      
 select  cte.ParentID, c.ParentID  ,cte.Layer+1  
 From cte join PCategory c on cte.ParentID=c.CategoryID  and c.LanguageID=@LanguageID  
)  
   
 select * from PCategory  ca where  ca.LanguageID=@LanguageID  and ca.CategoryID in (select   categoryid  
 from   cte      where ParentID=@CategoryID group by categoryid,parentid)  AND  ca.CategoryID <>2147483647 order by ca.SortOrder,ca.CategoryID  
   
   
  
END  
END  